package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import edu.thu.cs.*;

/**
 * 这是连接MySql数据库的类
 * @author bo
 *
 */

public class JdbcDao {
	/**
	 * @param conn Connection
	 * @param stmt Statement
	 * @param password 数据库密码
	 * 
	 * @param URL jdbc连接
	 * @param DATABASENAME 数据库名称
	 * @param username 数据库用户名
	 * @param COLUMN 数据库列数
	 */
    static Connection conn;      
    static Statement stmt;     
    private static String password = "gbngbn999";
    

//	public String TAG = this.getClass().getName();
	public static String URL = "jdbc:mysql://localhost:3306/";
	public String DATABASENAME = "info";
	public static String username = "root";
	public static final int USER_COLUMN = 15;
	public static final int CLASS_COLUMN = 8;
	public static final int ARTICLE_COLUMN = 7;
	public static final String[] USER_INFO_TYPE = {
		"email", "admin", "realname", 
		"nickname", "gender", "phonenumber", "emercont", 
		"emercontnum", "address", "qq", "password", "lastlogin", "session", "classlist", "birthdate"
	};
	public static final String[] CLASS_INFO_TYPE = {
		"number", "name", "credit", "teacher", "numlimit", "tempnum", "classdate", "studentlist"
	};
	public static final String[] ARTICLE_INFO_TYPE = {
		"articleid", "title", "time", "authoremail", "tags", "content", "commentto"
	};
	
	public static int articleNumber = 0;
	
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 获取连接
	 * @return 获取的连接
	 */
	public Connection getConn() {
		System.out.println("getConn start");
		try {
			return DriverManager.getConnection(URL + DATABASENAME + "?characterEncoding=utf8", username, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 关闭数据库
	 * @param rs ResultSet
	 * @param ps Statement
	 * @param conn Connection
	 */
	public void release(ResultSet rs, Statement ps, Connection conn) {
		try {
			if (null != rs) {

				rs.close();
			}
			if (null != ps) {
				ps.close();
			}
			if (null != conn) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	/**
	 * 以下是查看users数据库的方法。
	 */
	
	/**
	 * 查看user某项信息
	 * @param email 目标用户的key
	 * @param infotype 在SQL中要查看的条目(admin/realname/nickname/gender/
	 * /phonenumber/emercont/emercontnum/address/qq/password )
	 * @return info
	 */
	public String getPersonalInfo(String email, String infotype) {
		String ans = new String();
		try {
			Connection conn = getConn();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from users where email = '" + email + "'");
			if (rs.next())
				ans = rs.getString(infotype);
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ans;
	}
	
	/**
	 * 获取用户的所有信息。
	 * @param email 目标用户的key
	 * @return 包含用户所有信息的hashmap
	 */
	public HashMap<String, String> getAllPersonalInfo(String email) {
		HashMap<String, String> hm = new HashMap<String, String>();
		try {
			Connection conn = getConn();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from users where email = '" + email + "'");
			if (rs.next())
				for (int i = 0; i < USER_COLUMN; ++i) {
					hm.put(USER_INFO_TYPE[i], rs.getString(USER_INFO_TYPE[i]));
				}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return hm;
	}
	/**
	 * 重载上面的方法，获取用户的所有信息。
	 * @param rs 目标用户的ResultSet
	 * @return 包含用户所有信息的hashmap
	 */
	public HashMap<String, String> getAllPersonalInfo(ResultSet rs) {
		HashMap<String, String> hm = new HashMap<String, String>();
		try {		
			for (int i = 0; i < USER_COLUMN; ++i) {
				hm.put(USER_INFO_TYPE[i], rs.getString(USER_INFO_TYPE[i]));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return hm;
	}
	
	/**
	 * 获取用户列表，由管理员使用
	 * @return 用户信息List
	 */
	public List<HashMap<String, String>> getUserList() {
		List<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
		try {
			Connection conn = getConn();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from users");
			while (rs.next()) {
				list.add(this.getAllPersonalInfo(rs));
				System.out.println("student email: " + rs.getString("email"));
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Collections.reverse(list);
		return list;
	}
	
	/**
	 * 一下是更改users数据库的方法。
	 */
	
	/**
	 * 新建用户
	 * @return 操作是否成功
	 */
	public boolean insertToUsers() {         
		Connection conn = getConn(); // 首先要获取连接，即连接到数据库   
        try { 
        	  // 插入数据的sql语句
            String sql = "INSERT INTO users(email, admin, realname, gender) VALUES('email1@qq.com', "
            		+ "'admin', '高博', 'male')";              
            Statement stmt = (Statement) conn.createStatement();    // 创建用于执行静态sql语句的Statement对象                
            stmt.executeUpdate(sql);  // 执行插入操作的sql语句，并返回插入数据的个数                
            System.out.println("成功插入数据！"); //输出插入操作的处理结果                
            conn.close();   //关闭数据库连接              
            return true;
        } catch (SQLException e) {  
            System.out.println("插入数据失败" + e.getMessage());  
            return false;
        }  
    }

	/**
	 * 修改用户权限，注意只有管理员可以调用
	 * @param email 目标用户的key
	 * @param admin 目标权限(admin/user/forbidden)
	 * @return 操作是否成功
	 */
	public boolean changeAdmin(String email, String admin) {
		Connection conn = getConn();
		try {
			String sql = "update users set admin = '" + admin + "' where email = '" + email +"'";
			Statement stmt = (Statement) conn.createStatement();
			stmt.executeUpdate(sql);
			System.out.println("成功修改用户权限");
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	/**
	 * 修改自己的昵称
	 * @param email 目标用户的key
	 * @param nickname 目标昵称
	 * @return 操作是否成功
	 */
	public boolean changeNickname(String email, String nickname) {
		Connection conn = getConn();
		try {
			String sql = "update users set nickname = '" + nickname +"' where email = '" + email + "'";
			Statement stmt = (Statement) conn.createStatement();
			stmt.executeUpdate(sql);
			System.out.println("成功修改昵称");
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	/**
	 * 修改自己的密码
	 * @param email 目标用户的key
	 * @param password 目标密码
	 * @return 操作是否成功
	 */
	public boolean changePassword(String email, String password) {
		Connection conn = getConn();
		try {
			String sql = "update users set password = '" + password +"' where email = '" + email + "'";
			Statement stmt = (Statement) conn.createStatement();
			stmt.executeUpdate(sql);
			System.out.println("成功修改昵称");
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	/**
	 * 修改user内容
	 * @param email 目标用户的key
	 * @param infotype 在SQL中要修改的条目(admin/realname/nickname/gender/
	 * /phonenumber/emercont/emercontnum/address/qq/password )
	 * @param newContent 新内容
	 * @return 操作是否成功
	 */
	public boolean changeUserContent(String email, String infotype, String newContent) {
		Connection conn = getConn();
		try {
			String sql = "update users set " + infotype + " = '" + newContent + "' where email = '" + email + "'";
			Statement stmt = (Statement) conn.createStatement();
			stmt.executeUpdate(sql);
			System.out.println("成功修改" + infotype + "为： " + newContent);
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	/**
	 * 修改user的最后登录时间
	 * @param email 目标用户的key
	 * @return 操作是否成功
	 */
	public boolean setLastlogin(String email) {
		Connection conn = getConn();
		try {
			String sql = "update users set lastlogin = NOW() where email = '" + email + "'";
			Statement stmt = (Statement) conn.createStatement();
			stmt.executeUpdate(sql);
			System.out.println("成功修改 lastlogin");
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public Statement excuteCommand(String command) {
		try {
			if (conn == null) {
				conn = DriverManager.getConnection(URL + DATABASENAME
						+ "?characterEncoding=utf8", username, password);
			}
			System.out.println("run command : " + command);
			stmt = (Statement) conn.createStatement();
			stmt.execute(command);
			return stmt;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	

	
	
	/**
	 * 添加于2014/9/20/14:00
	 */
	
	/**
	 * 新建class，管理员使用
	 * @param number
	 * @param name
	 * @param credit
	 * @param teacher
	 * @param numlimit
	 * @param classdate
	 * @return 操作是否成功
	 */
	public boolean newClass(
			String number, 
			String name, 
			String credit, 
			String teacher,
			int numlimit,
			String classdate
			) 
	{
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		java.util.Date dDate = new java.util.Date();
		Timestamp timeStamp = null;
		try {
			dDate = sdf.parse(classdate.trim());
			timeStamp = new Timestamp(dDate.getTime());
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		this.excuteCommand("use info;");
		if (this.excuteCommand("INSERT INTO classes (" +
				  "number,"        +
				  "name,"          +
				  "credit,"        +
				  "teacher,"       +
				  "numlimit,"      +
				  "tempnum, "	   +
				  "classdate"      +
				  ")" +
				  "VALUES(" +
				  "'" + number + "'," +
				  "'" + name  + "'," +
				  "'" + credit  + "'," +
				  "'" + teacher  + "'," +
				  numlimit + "," +
				  0 + "," +
				  "'" + timeStamp + "'" +
				  ")"
				  ) != null)
			return true;
		else
			return false;
	}
	/**
	 * 修改class的信息，管理员使用(注意studentlist最好使用单独的接口)
	 * @param number
	 * @param infoType
	 * @param newContent
	 * @return 操作是否成功
	 */
	public boolean changeClassInfo(String number, String infoType, String newContent) {
		if (this.excuteCommand("update classes set " + infoType + " = '" + newContent + " where number = '" + number + "'")
				!= null)
			return true;
		else return false;
	}
	
	/**
	 * 删除课程，管理员使用
	 * @param number
	 * @return 操作是否成功(如果课程不存在，回返回false)
	 */
	public boolean delClass(String number) {
		try {
			Connection conn = getConn();
			Statement stmt = (Statement) conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from classes where number = '" + number + "'");
			if (rs.next()) {
				String classList = rs.getString("studentlist");
				if (classList != null) {
					String reg = "[0-9a-zA-Z]([-.\\w]*[0-9a-zA-Z])*@([0-9a-zA-Z][-\\w]*[0-9a-zA-Z]\\.)+[a-zA-Z]{2,9}";
					Pattern pat = Pattern.compile(reg);
					Matcher mat = pat.matcher(classList);
					while (mat.find()) {
						String tempEmail = mat.group();
						delUserClass(tempEmail, number);
						System.out.println(tempEmail + " " + number);
					}
				}
				stmt.execute("delete from classes where number = '" + number + "'");				
				return true;
					
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	/**
	 * 删除某个人的某一门课。
	 * @param email(must be valid)
	 * @param number(class number may not exist)
	 * @return 相应的信息
	 */
	public String delUserClass(String email, String number) {
		ResultSet rs1 = null;
		ResultSet rs2 = null;
		Statement stmt2 = null;
		Statement stmt = null;
		try {
			String []ans = {
					"user email invalid.", 
					"successful!"
					};
			Connection conn = getConn();
			String sql1 = "select * from users where email = '" + email + "'";
			String sql2 = "select * from classes where number = '" + number + "'";
			stmt = (Statement) conn.createStatement();
			stmt2 = (Statement) conn.createStatement();
			rs1 = stmt.executeQuery(sql1);
			rs2 = stmt2.executeQuery(sql2);
			if (!rs1.next()) {
				return ans[0];
			} else {
				String classList = rs1.getString("classlist");
				if (classList == null)
					return null;
				classList = classList.replaceAll("," + number + ",", ",");
				stmt.executeUpdate("update users set classlist = '" + classList + "' where email = '" + email +"'");
				if (rs2.next()) {
					String studentList = rs2.getString("studentlist");
					int tempnum = rs2.getInt("tempnum");
					if (studentList == null)
						return null;
					studentList.replaceAll("," + email + ",", ",");
					stmt.executeUpdate("update classes set studentlist = '" + studentList + "' where number = '" + number+"'");
					stmt.executeUpdate("update classes set tempnum = " + --tempnum + " where number = '" + number+"'");
				}
				
				return ans[1];				
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				rs1.close();
				rs2.close();
				stmt.close();
				stmt2.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 查看某个课程是否在某个用户的classlist中。
	 * @param email
	 * @param number
	 * @return 是否找到
	 */
	public boolean inClassList(String email, String number) {
		try {	
			Connection conn = getConn();
			Statement stmt = (Statement) conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from users where email = '" + email + "'");
			if (rs.next()) {
				String classList = rs.getString("classlist");
				if (classList != null && classList.contains("," + number + ","))
					return true;
				else
					return false;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
		}
	}
	
	/**
	 * 获取我的class列表
	 * @param email
	 * @return
	 */
	public List<HashMap<String, String>> getMyClassList(String email) {
		List<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
		try {
			Connection conn = getConn();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from users where email = '" + email + "'");
			if (rs.next()) {
				String classList = rs.getString("classlist");
				if (classList == null)
					return list;
				Pattern pat = Pattern.compile("[0-9]+");
				Matcher mat = pat.matcher(classList);
				while (mat.find()) {
					String tempNum= mat.group();
					list.add(this.getAllClassInfo(tempNum));
				}
			}
			else {
				System.out.println("email invalid, can get class");
				return list;
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Collections.reverse(list);
		return list;
	}
	
	
	/**
	 * 选课操作
	 * @param email
	 * @param number
	 * @return 相应的提示信息
	 */
	public String chooseClass(String email, String number) {
		try {
			String []ans = {
					"user email invalid.", 
					"class number invalid.", 
					"you have already chosen that class",
					"successful!",
					"课容量不足"
					};
			Connection conn = getConn();
			String sql1 = "select * from users where email = '" + email + "'";
			String sql2 = "select * from classes where number = '" + number + "'";
			Statement stmt = (Statement) conn.createStatement();
			Statement stmt2 = (Statement) conn.createStatement();
			ResultSet rs1 = stmt.executeQuery(sql1);
			ResultSet rs2 = stmt2.executeQuery(sql2);

			if (!rs1.next()) {
				return ans[0];
			} else if (!rs2.next()) {
				return ans[1];
			} else if (inClassList(email, number)) {
				return ans[2];
			} else {
				int tempNum = rs2.getInt("tempnum");
				int limit = rs2.getInt("numlimit");
				if (tempNum >= limit)
					return ans[4];
				String classList = rs1.getString("classlist");
				String studentList = rs2.getString("studentlist");
				System.out.println(studentList + " " + classList);
				classList = (classList == null) ? new String("," + number +",") : classList + number + ",";
				studentList = (studentList == null) ? new String("," + email + ",") : studentList + email +",";
				stmt.executeUpdate("update users set classlist = '" + classList + "' where email = '" + email +"'");
				stmt.executeUpdate("update classes set studentlist = '" + studentList + "' where number = '" + number+"'");
				stmt.executeUpdate("update classes set tempnum = " + (tempNum + 1) + " where number = '" + number+"'");
			}
			System.out.println("选课成功");
			rs1.close();
			rs2.close();
			stmt.close();
			stmt2.close();
			conn.close();
			return ans[3];
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	/**
	 * 查看课程某项信息
	 * @param number 目标课程的key
	 * @param infotype 在SQL中要查看的条目("number", "name", "credit", "teacher", "numlimit"
	 * , "tempnum", "classdate", "studentlist")
	 * @return info
	 */
	public String getClassInfo(String number, String infotype) {
		String ans = new String();
		try {
			Connection conn = getConn();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from classes where number = '" + number + "'");
			if (rs.next())
				ans = rs.getString(infotype);
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ans;
	}
	
	public boolean inTwoDay(String number) {
		String classTime = getClassInfo(number, "classdate");
		if (classTime == null)
			return false;
		
		String nowTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
		Calendar c = Calendar.getInstance();
		c.setTime(new Date());  
        int day = c.get(Calendar.DATE);  
        c.set(Calendar.DATE, day + 2);  
  
        String targetTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(c.getTime());  
        if (classTime.compareTo(targetTime) < 0 && classTime.compareTo(nowTime) > 0)
        	return true;
        else
        	return false;
	}
	/**
	 * 获取两天之内的课程
	 * @return
	 */
	public List<HashMap<String, String>> getClassInTwoDay(String email) {
		List<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
		try {
			Connection conn = getConn();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from users where email = '" + email + "'");
			if (rs.next()) {
				String classList = rs.getString("classlist");
				if (classList == null)
					return list;
				Pattern pat = Pattern.compile("[0-9]+");
				Matcher mat = pat.matcher(classList);
				while (mat.find()) {
					String tempNum= mat.group();
					if (this.inTwoDay(tempNum))
						list.add(this.getAllClassInfo(tempNum));
				}
			}
			else {
				System.out.println("email invalid, can get class");
				return list;
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Collections.reverse(list);
		return list;
	}
	
	
	/**
	 * 获取某课程的所有信息。
	 * @param number 目标用户的key
	 * @return 包含用户所有信息的hashmap
	 */
	public HashMap<String, String> getAllClassInfo(String number) {
		HashMap<String, String> hm = new HashMap<String, String>();
		try {
			Connection conn = getConn();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from classes where number = '" + number + "'");
			if (rs.next())
				for (int i = 0; i < CLASS_COLUMN; ++i) {
					hm.put(CLASS_INFO_TYPE[i], rs.getString(CLASS_INFO_TYPE[i]));
				}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return hm;
	}
	/**
	 * 重载上面的方法，获取课程的所有信息。
	 * @param rs 目标课程的ResultSet
	 * @return 包含课程所有信息的hashmap
	 */
	public HashMap<String, String> getAllClassInfo(ResultSet rs) {
		HashMap<String, String> hm = new HashMap<String, String>();
		try {		
			for (int i = 0; i < CLASS_COLUMN; ++i) {
				hm.put(CLASS_INFO_TYPE[i], rs.getString(CLASS_INFO_TYPE[i]));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return hm;
	}
	
	/**
	 * 获取课程列表，由管理员使用
	 * @return 课程信息List
	 */
	public List<HashMap<String, String>> getClassList() {
		List<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
		try {
			Connection conn = getConn();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from classes");
			while (rs.next()) {
				list.add(this.getAllClassInfo(rs));
				System.out.println("Class number: " + rs.getString("number"));
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Collections.reverse(list);
		return list;
	}
	
	/**
	 * 按number搜索课程
	 * @param number
	 * @return number中含有输入字符串的class信息
	 */
	public List<HashMap<String, String>> searchClassByNumber(String number) {
		List<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
		try {
			Connection conn = getConn();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from classes where number like '%" + number + "%'");
			while (rs.next()) {
				list.add(this.getAllClassInfo(rs));
				System.out.println("Class number: " + rs.getString("number"));
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Collections.reverse(list);
		return list;
	}
	
	/**
	 * 按number搜索课程
	 * @param name
	 * @return number中含有输入字符串的class信息
	 */
	public List<HashMap<String, String>> searchClassByName(String name) {
		List<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
		try {
			Connection conn = getConn();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from classes where name like '%" + name + "%'");
			while (rs.next()) {
				list.add(this.getAllClassInfo(rs));
				System.out.println("Class number: " + rs.getString("number"));
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Collections.reverse(list);
		return list;
	}
	
	/**
	 * 以下是讨论区操作
	 */
	
	/**
	 * 发帖，commentto指向自己reply的对象。如果不是评论别人的，就为0
	 * @param title
	 * @param authoremail
	 * @param content
	 * @param commentto
	 * @return 发帖是否成功
	 */
	public int postArticle(String title, String authoremail, String content, int commentto) {//TODO: 试试int用string能不能传进去
		int id = -1;
		Date date = new Date();//插入现在时间
		Timestamp timeStamp = new Timestamp(date.getTime());
		try {
			Connection conn = getConn();
			Statement stmt = conn.createStatement();
			content = StaticFunc.mysqlRealEscapeString(content);
			stmt.execute("INSERT INTO articles (title, time, authoremail, content, commentto)"
				+ " VALUES('" + title + "', '" + timeStamp + "', '" + authoremail + "', '" + content + "', " 
				+ commentto + ")");
			ResultSet rs = stmt.executeQuery("select max(articleid) max_id from articles");
			if (rs.next())
			{
				id = rs.getInt("max_id");
			}
			stmt.close();
			conn.close();
			return id;
		} catch (Exception e) {
			e.printStackTrace();
			return id;
		}	
	}
	
	/**
	 * 发帖，commentto指向自己reply的对象。如果不是评论别人的，就为0
	 * @param title
	 * @param authoremail
	 * @param content
	 * @param commentto
	 * @return 发帖是否成功
	 */
	public int postArticle(String title, String authoremail, String content, String tag, int commentto) {//TODO: 试试int用string能不能传进去
		int id = -1;
		Date date = new Date();//插入现在时间
		Timestamp timeStamp = new Timestamp(date.getTime());
		try {
			Connection conn = getConn();
			Statement stmt = conn.createStatement();
			content = StaticFunc.mysqlRealEscapeString(content);
			stmt.execute("INSERT INTO articles (title, time, authoremail, content, tags, commentto)"
				+ " VALUES('" + title + "', '" + timeStamp + "', '" + authoremail + "', '" + content + "', '" 
				+ tag + "', " + commentto + ")");
			ResultSet rs = stmt.executeQuery("select max(articleid) max_id from articles");
			if (rs.next())
			{
				id = rs.getInt("max_id");
			}
			stmt.close();
			conn.close();
			return id;
		} catch (Exception e) {
			e.printStackTrace();
			return id;
		}	
	}
	

	
	/**
	 * 删除评论。管理员可以使用，用户在核对过email之后也可以使用。
	 * @param artNum [0, articleNum)
	 * @return
	 */
	public boolean delReply(int artNum) {
		try {
			Connection conn = getConn();
			Statement stmt = conn.createStatement();
			stmt.execute("use info");
			stmt.execute("delete from articles where articleid = " + artNum);
			stmt.close();
			conn.close();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	
	/**
	 * 更改article的词条
	 * @param artNum
	 * @param infoType
	 * @param newContent
	 * @return
	 */
	public boolean changeArticleInfo(int artNum, String infoType, String newContent) {
		try {
			Connection conn = getConn();
			Statement stmt = conn.createStatement();
			stmt.execute("use info");
			newContent = StaticFunc.mysqlRealEscapeString(newContent);
			stmt.execute("update articles set " + infoType + " = '" + newContent + "' where articleid = " + artNum);
			stmt.close();
			conn.close();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	
	/**
	 * 获取某个文章的信息
	 * @param artNum
	 * @return
	 */
	public HashMap<String, String> getArticleInfo(int artNum) {
		HashMap<String, String> hm = new HashMap<String, String>();
		try {
			Connection conn = getConn();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from articles where articleid = " + artNum);
			if (rs.next())
				for (int i = 0; i < ARTICLE_COLUMN; ++i) {
					String str = rs.getString(ARTICLE_INFO_TYPE[i]);
					if (ARTICLE_INFO_TYPE[i].equals("content"))
						str = StaticFunc.mysqlEscapeString(str);
					hm.put(ARTICLE_INFO_TYPE[i], str);
				}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return hm;
	}
	/**
	 * 获取文章的所有信息。
	 * @param rs 目标课程的ResultSet
	 * @return 包含所有信息的hashmap
	 */
	public HashMap<String, String> getAllArticleInfo(ResultSet rs) {
		HashMap<String, String> hm = new HashMap<String, String>();
		try {
			for (int i = 0; i < ARTICLE_COLUMN; ++i) {
				String str = rs.getString(ARTICLE_INFO_TYPE[i]);
				if (ARTICLE_INFO_TYPE[i].equals("content"))
					str = StaticFunc.mysqlEscapeString(str);
				hm.put(ARTICLE_INFO_TYPE[i], str);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return hm;
	}
	
	/**
	 * 获取文章列表，由管理员使用
	 * @return 课程信息List
	 */
	public List<HashMap<String, String>> getArticleList() {
		List<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
		try {
			Connection conn = getConn();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from articles");
			while (rs.next()) {
				list.add(this.getAllArticleInfo(rs));
				System.out.println("Article number: " + rs.getString("articleid"));
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Collections.reverse(list);
		return list;
	}
	
	/**
	 * 按commentto搜索文章
	 * @param commentto
	 * @return number中含有输入字符串的class信息
	 */
	public List<HashMap<String, String>> searchArticleByCommentto(int commentto) {
		List<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
		try {
			Connection conn = getConn();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from articles where commentto = " + commentto);
			while (rs.next()) {
				list.add(this.getAllArticleInfo(rs));
				System.out.println("Article number: " + rs.getString("articleid"));
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Collections.reverse(list);
		return list;
	}
	
	/**
	 * 按tag模糊搜索文章
	 * @param tag
	 * @return
	 */
	public List<HashMap<String, String>> searchArticleByTag(String tag) {
		List<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
		try {
			Connection conn = getConn();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from articles where tags like '%" + tag + "%'");
			while (rs.next()) {
				list.add(this.getAllArticleInfo(rs));
				System.out.println("Article number: " + rs.getString("articleid"));
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Collections.reverse(list);
		return list;
	}
	
	
}
